
<?php

abstract class produtosDAOImpl {
    public static function Select() {
        if (DB::$mysqli = DB::Connection()) {
            $query = '(SELECT "F" AS type,
                    ptoo.`id_client`,
                    cl.`name` AS clname,
                    pl.name,
                    pl.price
                    FROM `products_owners` AS ptoo
                    INNER JOIN `customers` AS cl ON (cl.`id`=ptoo.`id_client` AND ptoo.`type`="F")
                    LEFT JOIN `products_transactions` AS ptot ON (ptot.`id_client`=ptoo.`id_client` AND ptot.`type`=ptoo.`type`)
                    INNER JOIN `products_client_status` AS pcs ON (pcs.`id_client`=ptoo.`id_client` AND pcs.`type`=ptoo.`type`)
                    INNER JOIN `plans` AS pl ON (pl.`id`=cl.`id_product_plan`)
                    WHERE (DATE_ADD(ptot.`paydate`, INTERVAL pl.`daysamount` DAY)<CURDATE() OR ptot.`paydate` is NULL)
                    and cl.`alertpay`=1 and (pcs.`id_status`=1 or pcs.`id_status`=4)
                    GROUP BY cl.`id`
                    )
                    UNION
                    (SELECT
                        "J" AS type,
                        ptoo.`id_client`,
                        cl.`fancyname` AS clname,
                        pl.name,
                        pl.price
                    FROM `products_owners` AS ptoo
                    INNER JOIN `enterprises` AS cl ON (cl.`id`=ptoo.`id_client` AND ptoo.`type`="J")
                    LEFT JOIN `products_transactions` AS ptot ON (ptot.`id_client`=ptoo.`id_client` AND ptot.`type`=ptoo.`type`)
                    INNER JOIN `products_client_status` AS pcs ON (pcs.`id_client`=ptoo.`id_client` AND pcs.`type`=ptoo.`type`)
                    INNER JOIN `plans` AS pl ON (pl.`id`=cl.`id_product_plan`)
                    WHERE (DATE_ADD(ptot.`paydate`, INTERVAL pl.`daysamount` DAY)<CURDATE() OR ptot.`paydate` is NULL)
                    and cl.`alertpay`=1 and (pcs.`id_status`=1 or pcs.`id_status`=4)
                    GROUP BY cl.`id`
                    )ORDER BY clname';
            DB::$_stmt = DB::$mysqli->prepare($query);
            if (!DB::$_stmt->execute()) {
                DB::$mysqli->error;
            }
            DB::$_stmt->bind_result($data_type,$data_id,$data_name,$data_plan,$data_price);
            while (DB::$_stmt->fetch()) {
                $data[] = array(
                    'type'=>$data_type,
                    'id'=>$data_id,
                    'name'=>$data_name,
                    'plan'=>$data_plan,
                    'price'=>$data_price
                );
            }
            return $data;
        } else {
            error_log("erro ao conectar!");
        }
    }

    public static function CreateTransaction($id, $id_client,$type) {
        if (DB::$mysqli = DB::Connection()) {
            $query = "insert into products_transactions
                (id, id_client,type) 
                values (?,?,?)";
            DB::$_stmt = DB::$mysqli->prepare($query);
            DB::$_stmt->bind_param('sis', $id, $id_client,$type);
            if (!DB::$_stmt->execute()) {
                echo DB::$mysqli->error;
            }
            return DB::$_stmt;
        } else {
            error_log("erro ao conectar!");
        }
    }
    public static function UpdateStatus($id_client,$type,$status) {
        if (DB::$mysqli = DB::Connection()) {
            $query = "update products_client_status
                set id_status =? where type=? and id_client=?";
            DB::$_stmt = DB::$mysqli->prepare($query);
            DB::$_stmt->bind_param('isi',$status,$type,$id_client);
            if (!DB::$_stmt->execute()) {
                echo DB::$mysqli->error;
            }
            return DB::$_stmt;
        } else {
            error_log("erro ao conectar!");
        }
    }

}

?>
